﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Drawing;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace HarveyNash.RefactoringAndCodeSmell.CodeSmell.LongMethod
{
    public static class Before
    {
        public static void WriteToExcel(DataSet ds, String fileName, bool append, string excelConnectionString)
        {
            OleDbConnection excelFile = null;
            OleDbCommand excelCommand = null;
            OleDbDataAdapter excelDataAdapter = null;
            OleDbCommandBuilder excelCommandBuilder = null;
            StringBuilder sb = null;
            try
            {
                if (File.Exists(fileName) && !append) File.Delete(fileName);
                excelFile = new OleDbConnection(excelConnectionString);
                excelFile.Open();
                foreach (DataTable dt in ds.Tables)
                {
                    if (!File.Exists(fileName) || !append)
                    {
                        sb = new StringBuilder();
                        sb.AppendFormat("CREATE TABLE [{0}] (", dt.TableName);
                        foreach (DataColumn dc in ds.Tables[dt.TableName].Columns)
                        {
                            sb.AppendFormat("[{0}] {1}{2}", dc.ColumnName,
                                getColumnType(dc)
                                , (dc.Ordinal == dt.Columns.Count - 1 ?
                                ")" : ","));
                        }
                        excelCommand = new OleDbCommand(sb.ToString(), excelFile);
                        excelCommand.ExecuteNonQuery();
                    }
                    excelDataAdapter = new OleDbDataAdapter(string.Format("select * from [{0}]", dt.TableName), excelFile);
                    excelCommandBuilder = new OleDbCommandBuilder(excelDataAdapter);
                    excelCommandBuilder.QuotePrefix = "[";
                    excelCommandBuilder.QuoteSuffix = "]";

                    excelDataAdapter.InsertCommand = excelCommandBuilder.GetInsertCommand();
                    excelDataAdapter.Update(ds, dt.TableName);

                }
            }
            finally
            {
                sb = null;
                if (excelDataAdapter != null) excelDataAdapter.Dispose();
                excelDataAdapter = null;
                excelCommandBuilder = null;
                if (excelCommand != null) excelCommand.Dispose();
                excelCommand = null;
                if (excelFile != null)
                {
                    excelFile.Close();
                    excelFile.Dispose();
                }
                excelFile = null;
            }
        }

        private static string getColumnType(DataColumn dc)
        {
            string columnType = "TEXT";
            switch (dc.DataType.ToString())
            {
                case "System.Int64":
                case "System.Double":
                case "System.Int32":
                    columnType = "NUMERIC";
                    break;
                default:
                    columnType = "TEXT";
                    break;
            }
            return columnType;
        }
    }
    
}

